import * as XLSX from 'xlsx';
import type { Trainer } from '../types/trainer';
import { DiseaseService } from '../api/service/diseaseService';
import { CheckItemService } from '../api/service/checkItemService';
import { DoctorService } from '../api/service/doctorService';

export class ExcelParser {
  /**
   * 解析Excel文件为患者数据数组
   * @param file Excel文件
   * @returns 患者数据数组
   */
  static async parseTrainerExcel(file: File): Promise<Trainer[]> {
    const arrayBuffer = await file.arrayBuffer();
    const workbook = XLSX.read(arrayBuffer);
    
    // 查找主工作表（患者模板）
    const mainSheetName = workbook.SheetNames.find(name => name.includes('患者模板')) || workbook.SheetNames[0];
    const worksheet = workbook.Sheets[mainSheetName];
    
    // 将工作表转换为JSON
    const jsonData = XLSX.utils.sheet_to_json(worksheet);
    
    // 从Excel中读取字典数据（如果存在）
    let diseaseListFromExcel: any[] = [];
    let checkItemsListFromExcel: any[] = [];
    let doctorListFromExcel: any[] = [];
    
    // 尝试从Excel中读取疾病字典
    const diseaseSheetName = workbook.SheetNames.find(name => name.includes('疾病字典'));
    if (diseaseSheetName) {
      const diseaseWorksheet = workbook.Sheets[diseaseSheetName];
      const diseaseData = XLSX.utils.sheet_to_json(diseaseWorksheet, { header: 1 });
      // 跳过标题行，从第二行开始
      diseaseListFromExcel = diseaseData.slice(1).map((row: any) => ({
        id: row[0], // 第一列是值
        name: row[1] // 第二列是标签
      })).filter(item => item.id && item.name);
    }
    
    // 尝试从Excel中读取检查项目字典
    const checkItemSheetName = workbook.SheetNames.find(name => name.includes('检查项目字典'));
    if (checkItemSheetName) {
      const checkItemWorksheet = workbook.Sheets[checkItemSheetName];
      const checkItemData = XLSX.utils.sheet_to_json(checkItemWorksheet, { header: 1 });
      checkItemsListFromExcel = checkItemData.slice(1).map((row: any) => ({
        id: row[0], // 第一列是值
        name: row[1] // 第二列是标签
      })).filter(item => item.id && item.name);
    }
    
    // 尝试从Excel中读取医生字典
    const doctorSheetName = workbook.SheetNames.find(name => name.includes('医生字典'));
    if (doctorSheetName) {
      const doctorWorksheet = workbook.Sheets[doctorSheetName];
      const doctorData = XLSX.utils.sheet_to_json(doctorWorksheet, { header: 1 });
      doctorListFromExcel = doctorData.slice(1).map((row: any) => ({
        id: row[0], // 第一列是值
        name: row[1] // 第二列是标签
      })).filter(item => item.id && item.name);
    }
    
    // 优先使用Excel中的字典数据，如果没有则从数据库获取
    const diseaseList = diseaseListFromExcel.length > 0 ? diseaseListFromExcel : await DiseaseService.list();
    const checkItemsList = checkItemsListFromExcel.length > 0 ? checkItemsListFromExcel : await CheckItemService.list();
    const doctorList = doctorListFromExcel.length > 0 ? doctorListFromExcel : await DoctorService.list();
    
    // 映射Excel列到患者字段
    return jsonData.map((row: any) => {
      // 处理性别转换
      const genderMap: Record<string, number> = {
        '男': 1,
        '女': 2
      };
      
      // 处理诊断疾病转换（多个疾病用逗号或中文逗号分隔）
      const diseaseNames = (row['诊断疾病（疾病字典，多个用逗号隔开）'] || '').toString()
        .split(/[,，]/) // 支持英文逗号和中文逗号
        .map((name: string) => name.trim())
        .filter(Boolean);
      const diseaseIds = diseaseNames.map((name: string) => {
        const disease = diseaseList.find(d => d.name === name);
        return disease ? disease.id : null;
      }).filter((id): id is number => id !== null);
      
      // 处理检查项目转换（多个项目用逗号或中文逗号分隔）
      const checkItemNames = (row['检查项目（检查项目字典，多个用逗号隔开）'] || '').toString()
        .split(/[,，]/) // 支持英文逗号和中文逗号
        .map((name: string) => name.trim())
        .filter(Boolean);
      const checkItemIds = checkItemNames.map((name: string) => {
        const item = checkItemsList.find(c => c.name === name);
        return item ? item.id : null;
      }).filter((id): id is number => id !== null);
      
      // 处理责任医生转换
      const chargeDoctorName = (row['责任医生(医生字典)'] || '').toString().trim();
      const chargeDoctor = doctorList.find(d => d.name === chargeDoctorName);
      
      // 处理主训医生转换
      const trainDoctorName = (row['康复师(医生字典)'] || '').toString().trim();
      const trainDoctor = doctorList.find(d => d.name === trainDoctorName);
      
      // 处理年龄转换，确保是数字
      let age = 0;
      const ageValue = row['年龄'];
      if (typeof ageValue === 'number') {
        age = ageValue;
      } else if (typeof ageValue === 'string') {
        age = parseInt(ageValue) || 0;
      }
      
      const trainer = {
        id: 0, // 数据库自增id
        name: (row['姓名'] || '').toString().trim(),
        gender: genderMap[(row['性别'] || '').toString().trim()] || 0,
        age: age,
        birthday: this.validateDateFormat((row['出生日期（yyyy/MM/dd）'] || '').toString().trim()),
        create_time:`${new Date().getFullYear()}-${new Date().getMonth() + 1}-${new Date().getDate()}`  , //yyyy-MM-dd
        contact: (row['家长'] || '').toString().trim(),
        tel: (row['联系电话'] || '').toString().trim(),
        address: (row['家庭地址'] || '').toString().trim(),
        disease: diseaseIds.length > 0 ? JSON.stringify(diseaseIds) : '',
        medical_history: (row['病史'] || '').toString().trim(),
        check_items: checkItemIds.length > 0 ? JSON.stringify(checkItemIds) : '',
        check_result: (row['检查情况'] || '').toString().trim(),
        train_doctor: trainDoctor ? trainDoctor.id : 0,
        charge_doctor: chargeDoctor ? chargeDoctor.id : 0
      } as Trainer;
      console.log(trainer)
      return trainer;
    });
  }

  /**
   * 生成患者Excel模板
   * @returns Blob对象
   */
  static async generateTemplate(): Promise<Blob> {
    // 获取数据字典
    const diseaseList = await DiseaseService.list();
    const checkItemsList = await CheckItemService.list();
    const doctorList = await DoctorService.list();
    
    const headers = [
      '姓名', '性别', '年龄', '出生日期（yyyy/MM/dd）', 
      '家长', '联系电话', '家庭地址', '诊断疾病（疾病字典，多个用逗号隔开）', '病史',
      '检查项目（检查项目字典，多个用逗号隔开）', '检查情况', '康复师(医生字典)', '责任医生(医生字典)'
    ];
    
    // 创建主工作表
    const worksheet = XLSX.utils.aoa_to_sheet([headers]);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, '患者模板');

    // 设置列宽
    worksheet['!cols'] = [
      { width: 15 }, // A列: 姓名 (15字符宽度)
      { width: 8 },  // B列: 性别 (8字符宽度)
      { width: 8 },  // C列: 年龄 (8字符宽度)
      { width: 25 }, // D列: 出生日期 (12字符宽度)
      { width: 15 }, // F列: 家长 (15字符宽度)
      { width: 15 }, // G列: 联系电话 (15字符宽度)
      { width: 25 }, // H列: 家庭地址 (25字符宽度)
      { width: 40 }, // I列: 诊断疾病 (20字符宽度)
      { width: 25 }, // J列: 病史 (25字符宽度)
      { width: 40 }, // K列: 检查项目 (20字符宽度)
      { width: 25 }, // L列: 检查情况 (25字符宽度)
      { width: 15 }, // M列: 康复师 (15字符宽度)
      { width: 15 }  // N列: 责任医生 (15字符宽度)
    ];
    
    // 添加性别字典工作表
    const genderData = [
      ['标签'],
      ['男'],
      ['女']
    ];
    const genderWorksheet = XLSX.utils.aoa_to_sheet(genderData);
    XLSX.utils.book_append_sheet(workbook, genderWorksheet, '性别字典');
    

    // 添加数据验证下拉列表
    // 性别下拉列表 (B列)
    worksheet['!dataValidation'] = worksheet['!dataValidation'] || [];
    worksheet['!dataValidation'].push({
      sqref: 'B2:B1048576', // B列从第2行开始
      type: 'list',
      formula1: '"男,女"',
      allowBlank: true
    });
    

    // 家庭结构下拉列表 (E列)
    worksheet['!dataValidation'].push({
      sqref: 'E2:E1048576', // E列从第2行开始
      type: 'list',
      formula1: '"三代同堂,核心家庭,单亲家庭"',
      allowBlank: true
    });
    
    // 诊断疾病下拉列表 (I列) - 使用命名范围
    const diseaseNames = diseaseList.map(d => d.name);
    const diseaseWorksheet = XLSX.utils.aoa_to_sheet([['疾病名称'], ...diseaseNames.map(name => [name])]);
    XLSX.utils.book_append_sheet(workbook, diseaseWorksheet, '疾病字典');
    
    worksheet['!dataValidation'].push({
      sqref: 'I2:I1048576', // I列从第2行开始
      type: 'list',
      formula1: '疾病字典!$A$2:$A$' + (diseaseNames.length + 1),
      allowBlank: true
    });
    
    // 检查项目下拉列表 (K列) - 使用命名范围
    const checkItemNames = checkItemsList.map(c => c.name);
    const checkItemWorksheet = XLSX.utils.aoa_to_sheet([['检查项目名称'], ...checkItemNames.map(name => [name])]);
    XLSX.utils.book_append_sheet(workbook, checkItemWorksheet, '检查项目字典');
    
    worksheet['!dataValidation'].push({
      sqref: 'K2:K1048576', // K列从第2行开始
      type: 'list',
      formula1: '检查项目字典!$A$2:$A$' + (checkItemNames.length + 1),
      allowBlank: true
    });
    
    // 主训医生下拉列表 (M列) - 使用命名范围
    const doctorNames = doctorList.map(d => d.name);
    const doctorWorksheet = XLSX.utils.aoa_to_sheet([['医生姓名'], ...doctorNames.map(name => [name])]);
    XLSX.utils.book_append_sheet(workbook, doctorWorksheet, '医生字典');
    
    worksheet['!dataValidation'].push({
      sqref: 'M2:M1048576', // M列从第2行开始
      type: 'list',
      formula1: '医生字典!$A$2:$A$' + (doctorNames.length + 1),
      allowBlank: true
    });
    
    // 责任医生下拉列表 (N列) - 使用命名范围
    worksheet['!dataValidation'].push({
      sqref: 'N2:N1048576', // N列从第2行开始
      type: 'list',
      formula1: '医生字典!$A$2:$A$' + (doctorNames.length + 1),
      allowBlank: true
    });


    // 添加使用说明工作表
    const instructionData = [
      ['使用说明'],
      ['1. 请在"患者模板"工作表中填写患者信息'],
      ['2. 性别：详见性别字典'],
      ['3. 诊断疾病：详见疾病字典，可多选（用逗号","或中文逗号"，"分隔）'],
      ['4. 检查项目：详见检查项目项目字典，可多选（用逗号","或中文逗号"，"分隔）']
      ['5. 责任医生/主训医生：详见医生字典'],
      ['6. 具体请参考各个字典工作表']
    ];
    
    const instructionWorksheet = XLSX.utils.aoa_to_sheet(instructionData);
    XLSX.utils.book_append_sheet(workbook, instructionWorksheet, '使用说明');
    
    const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    return new Blob([excelBuffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
  }

  /**
   * 验证日期格式是否为 yyyy-MM-dd
   * @param dateStr 日期字符串
   * @returns 验证后的日期字符串，如果格式不正确则返回空字符串
   */
  private static validateDateFormat(dateStr: string): string {
    if (!dateStr) return '';
    
    // yyyy-MM-dd 格式验证
    const dateRegex = /^\d{4}-\d{2}-\d{2}$/;
    if (dateRegex.test(dateStr)) {
      return dateStr;
    }
    
    // 尝试转换其他格式的日期
    try {
      const date = new Date(dateStr);
      if (!isNaN(date.getTime())) {
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        return `${year}-${month}-${day}`;
      }
    } catch (error) {
      // 转换失败，返回空字符串
    }
    
    return '';
  }
}